Using PreparedStatement for Parameterized Queries in Java JDBC

Course: Advanced Java Training

Topic: Using PreparedStatement for Parameterized Queries

Course Link: Advanced Java Training Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to PreparedStatement

Java Database Connectivity (JDBC) is an API that enables Java applications to connect to relational databases, execute SQL commands, retrieve records, and modify database information.

The PreparedStatement interface is part of the java.sql package. It allows developers to execute parameterized SQL queries by using placeholders represented by question marks (?). Values are supplied separately through methods such as setString(), setInt(), and setDouble().

PreparedStatement is especially useful when an application receives data from users, such as login credentials, student information, product details, or employee records. It helps separate SQL instructions from parameter values and reduces the risk of SQL injection when used correctly.

2. Learning Objectives

• Understand the purpose and features of PreparedStatement.
• Learn how parameterized SQL queries work.
• Create PreparedStatement objects using a JDBC Connection.
• Use parameter setter methods to provide values.
• Execute SELECT, INSERT, UPDATE, and DELETE queries.
• Understand the differences between Statement and PreparedStatement.
• Prevent common SQL injection vulnerabilities.
• Handle exceptions and manage JDBC resources safely.
• Build a practical database application using parameterized queries.

3. What Is PreparedStatement?

PreparedStatement is a JDBC interface that represents a SQL statement prepared for execution. It extends the Statement interface and supports parameter placeholders, allowing values to be supplied separately from the SQL command.

Depending on the database and JDBC driver, the SQL statement may be compiled or prepared in advance. PreparedStatement can also be reused with different parameter values, which may improve efficiency for repeated execution.

Basic Syntax

String sql =
    "SELECT * FROM students WHERE id = ?";

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setInt(1, 101);

ResultSet rs = ps.executeQuery();

In this example, the question mark represents a parameter. The setInt(1, 101) method assigns the integer value 101 to the first parameter.

4. Why Do We Need Parameterized Queries?

Applications frequently need to execute SQL queries using values entered by users. Constructing SQL commands through string concatenation can cause syntax errors and security vulnerabilities.

Example of an Unsafe Query

String username = userInput;

String sql =
    "SELECT * FROM users WHERE username = '"
    + username + "'";

This approach combines user input directly with SQL syntax. A malicious input may change the intended meaning of the query.

Safer Approach Using PreparedStatement

String sql =
    "SELECT * FROM users WHERE username = ?";

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setString(1, username);

The parameter value is bound separately instead of being inserted into the SQL string. The database treats a correctly bound value as data rather than SQL syntax.

5. Important JDBC Components

6. Creating a Database and Table

Before running the Java examples, create a sample database and table in MySQL.

CREATE DATABASE college_db;

USE college_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    marks DECIMAL(5,2)
);

INSERT INTO students (name, course, marks)
VALUES
('Amit', 'Java', 85.50),
('Priya', 'Python', 91.00),
('Rahul', 'SQL', 78.00);

This table stores student IDs, names, course names, and marks. The AUTO_INCREMENT property generates an ID when a new student is inserted without specifying one.

7. Establishing a Database Connection

A JDBC connection must be established before creating a PreparedStatement object. The following example uses MySQL and assumes that MySQL Connector/J is available in the project dependencies.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password)) {
            System.out.println(
                "Database connected successfully."
            );
        } catch (SQLException e) {
            System.err.println(
                "Connection failed: " + e.getMessage()
            );
        }
    }
}

Replace the example credentials with valid database credentials. For production applications, use environment variables or a secure configuration mechanism instead of hardcoding passwords.

8. Creating a PreparedStatement Object

The prepareStatement() method of the Connection interface creates a PreparedStatement object for a SQL string.

Syntax

PreparedStatement ps =
    connection.prepareStatement(sql);

Example

String sql =
    "SELECT id, name FROM students WHERE course = ?";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Java");

    System.out.println(
        "Parameterized statement prepared."
    );

} catch (SQLException e) {
    e.printStackTrace();
}

The SQL statement contains one placeholder. The value for that placeholder is supplied with setString() before execution.

9. Understanding Question Mark Placeholders

A question mark (?) is a parameter marker used to represent a value that will be supplied later. Parameter indexes start at 1, not 0.

Example with Multiple Parameters

String sql =
    "SELECT * FROM students " +
    "WHERE course = ? AND marks >= ?";

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setString(1, "Java");
ps.setDouble(2, 80.0);

ResultSet rs = ps.executeQuery();

Here, the first placeholder represents the course and the second represents the minimum marks.

Every placeholder should receive a suitable value before the statement is executed. The value type should be compatible with the corresponding SQL column or expression.

10. Common Parameter Setter Methods

PreparedStatement provides setter methods for binding Java values to SQL parameters.

11. Executing SELECT Queries with PreparedStatement

The executeQuery() method executes a query that returns a ResultSet. It is commonly used to retrieve records based on user input or application parameters.

Example: Search for a Student by ID

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SearchStudent {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        int studentId = 1;

        String sql =
            "SELECT id, name, course, marks " +
            "FROM students WHERE id = ?";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 connection.prepareStatement(sql)) {

            ps.setInt(1, studentId);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    System.out.println(
                        "ID: " + rs.getInt("id")
                    );
                    System.out.println(
                        "Name: " + rs.getString("name")
                    );
                    System.out.println(
                        "Course: " + rs.getString("course")
                    );
                    System.out.println(
                        "Marks: " + rs.getDouble("marks")
                    );
                } else {
                    System.out.println(
                        "Student not found."
                    );
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

• The SQL query contains one placeholder for the student ID.
• setInt(1, studentId) binds the ID value to the first parameter.
• executeQuery() executes the query and returns a ResultSet.
• rs.next() checks whether a matching row exists.
• Getter methods retrieve the values from the matching row.

12. Inserting Records Using PreparedStatement

The executeUpdate() method is used for INSERT operations. It returns the number of affected rows.

Example: Insert a Student

String sql =
    "INSERT INTO students (name, course, marks) " +
    "VALUES (?, ?, ?)";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Neha");
    ps.setString(2, "Java");
    ps.setDouble(3, 88.50);

    int rowsAffected = ps.executeUpdate();

    System.out.println(
        "Rows inserted: " + rowsAffected
    );

} catch (SQLException e) {
    e.printStackTrace();
}

Explanation

1. The INSERT statement contains three placeholders.
2. The first placeholder receives the student name.
3. The second placeholder receives the course name.
4. The third placeholder receives the marks.
5. The executeUpdate() method inserts the record.
6. The returned row count indicates the number of affected records.

13. Updating Records Using PreparedStatement

PreparedStatement is suitable for updating database records using values provided by an application.

Example: Update Student Marks

String sql =
    "UPDATE students SET marks = ? WHERE id = ?";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setDouble(1, 95.00);
    ps.setInt(2, 1);

    int rowsAffected = ps.executeUpdate();

    System.out.println(
        "Rows updated: " + rowsAffected
    );

} catch (SQLException e) {
    e.printStackTrace();
}

The first parameter sets the new marks, and the second parameter identifies the student. Using a WHERE clause prevents unintended updates to all records.

14. Deleting Records Using PreparedStatement

The DELETE statement removes selected records from a table. PreparedStatement allows the selection condition to be supplied as a parameter.

Example: Delete a Student by ID

String sql =
    "DELETE FROM students WHERE id = ?";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setInt(1, 3);

    int rowsAffected = ps.executeUpdate();

    System.out.println(
        "Rows deleted: " + rowsAffected
    );

} catch (SQLException e) {
    e.printStackTrace();
}

If the specified ID exists, the corresponding record is deleted. If no matching record exists, the method normally returns 0. Always verify the target condition before performing deletion operations.

15. Using Multiple Parameters in a Query

Parameterized queries can contain multiple placeholders to filter records by different conditions.

Example: Search Students by Course and Marks

String sql =
    "SELECT name, course, marks FROM students " +
    "WHERE course = ? AND marks >= ?";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Java");
    ps.setDouble(2, 80.00);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(
                rs.getString("name") + " | " +
                rs.getString("course") + " | " +
                rs.getDouble("marks")
            );
        }
    }

} catch (SQLException e) {
    e.printStackTrace();
}

This query returns students enrolled in Java whose marks are at least 80. Parameter indexes must correspond to the order of placeholders in the SQL string.

16. Reusing PreparedStatement

A PreparedStatement can be executed multiple times with different parameter values. This is useful when inserting or updating several records using the same SQL structure.

Example: Insert Multiple Students

String sql =
    "INSERT INTO students (name, course, marks) " +
    "VALUES (?, ?, ?)";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Karan");
    ps.setString(2, "Java");
    ps.setDouble(3, 82.00);
    ps.executeUpdate();

    ps.setString(1, "Meera");
    ps.setString(2, "Python");
    ps.setDouble(3, 89.50);
    ps.executeUpdate();

    System.out.println(
        "Student records inserted successfully."
    );

} catch (SQLException e) {
    e.printStackTrace();
}

The same prepared SQL structure is reused with different parameter values. JDBC drivers and databases may optimize repeated executions, although the exact performance benefits depend on the implementation.

17. Batch Processing with PreparedStatement

Batch processing allows multiple SQL operations to be submitted together. It can reduce database communication overhead when inserting or updating many records.

Example: Insert Records Using a Batch

String sql =
    "INSERT INTO students (name, course, marks) " +
    "VALUES (?, ?, ?)";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Aarav");
    ps.setString(2, "Java");
    ps.setDouble(3, 84.00);
    ps.addBatch();

    ps.setString(1, "Ananya");
    ps.setString(2, "SQL");
    ps.setDouble(3, 92.00);
    ps.addBatch();

    int[] results = ps.executeBatch();

    System.out.println(
        "Batch operations processed: " +
        results.length
    );

} catch (SQLException e) {
    e.printStackTrace();
}

The addBatch() method adds the current parameter set to the batch, and executeBatch() submits the collected operations. Returned counts may include special values depending on the JDBC driver.

For applications requiring all-or-nothing behavior, use a transaction and explicitly commit or roll back the batch as appropriate. Batch execution alone does not guarantee atomicity.

18. How PreparedStatement Helps Prevent SQL Injection

SQL injection occurs when untrusted input is combined with SQL in a way that changes the intended query structure. Parameterized queries help prevent this by separating parameter values from SQL syntax.

Unsafe Example

String username = userInput;

String sql =
    "SELECT * FROM users WHERE username = '"
    + username + "'";

If the input contains SQL syntax, string concatenation may allow the input to change the query's meaning.

Safer Example

String sql =
    "SELECT * FROM users WHERE username = ?";

try (PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, username);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(
                rs.getString("username")
            );
        }
    }
}

In this example, the username is bound as a parameter. It is treated as a value rather than being interpreted as part of the SQL command.

Important: PreparedStatement does not automatically secure every part of a SQL query. Table names, column names, and SQL keywords generally cannot be supplied as ordinary parameter values. If an application needs dynamic identifiers or sort directions, validate them against a strict allowlist.

19. Difference Between Statement and PreparedStatement

20. Common Errors and Troubleshooting

21. Best Practices for PreparedStatement

• Use PreparedStatement for SQL statements that contain variable values.
• Use setter methods that match the SQL parameter types.
• Ensure that every required placeholder is assigned a value before execution.
• Use try-with-resources to close connections, statements, and result sets.
• Avoid concatenating untrusted input into SQL strings.
• Validate user input even when using parameterized queries.
• Use allowlists for dynamic table names, column names, and sort directions.
• Use transactions when multiple operations must succeed or fail together.
• Use batch processing when appropriate for large numbers of similar operations.
• Keep database credentials secure and assign only necessary permissions.
• Handle SQLException appropriately and avoid exposing sensitive information in error messages.

22. Practical Project: Student Management System

Create a small Java application that uses PreparedStatement to manage student records in a MySQL database.

Project Requirements

• Connect to the college_db database.
• Search for a student using their ID.
• Insert new student records.
• Update a student's course or marks.
• Delete a student using a unique ID.
• Display all students with a SELECT query.
• Handle invalid inputs and SQL exceptions.
• Close JDBC resources correctly.

Sample Program: Adding a Student

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class StudentManager {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        String sql =
            "INSERT INTO students (name, course, marks) " +
            "VALUES (?, ?, ?)";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 connection.prepareStatement(sql)) {

            ps.setString(1, "Riya");
            ps.setString(2, "Advanced Java");
            ps.setDouble(3, 90.00);

            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println(
                    "Student added successfully."
                );
            } else {
                System.out.println(
                    "No student record was inserted."
                );
            }

        } catch (SQLException e) {
            System.err.println(
                "Unable to add student: " +
                e.getMessage()
            );
        }
    }
}

This program inserts one student using parameterized SQL. The example assumes the database table exists and that the supplied credentials have permission to insert records.

23. Practice Exercises

1. Write a PreparedStatement query to search for a student using an ID.
2. Retrieve students enrolled in a course specified by the user.
3. Insert a new student with a name, course, and marks.
4. Update marks using a student's ID.
5. Delete a student record using a parameterized condition.
6. Write a query with two placeholders for course and minimum marks.
7. Display the number of rows affected by an UPDATE query.
8. Insert multiple student records using batch processing.
9. Handle a missing parameter and observe the resulting error.
10. Compare a concatenated SQL query with its PreparedStatement equivalent.
11. Create a simple student search form that uses PreparedStatement.
12. Implement transaction handling for multiple related database operations.

24. Interview Questions and Answers

Q1. What is PreparedStatement in JDBC?

Answer: PreparedStatement is an interface in java.sql used to execute SQL statements with parameter placeholders and separately bound values.

Q2. How do you create a PreparedStatement?

Answer: Use the prepareStatement() method of a Connection object and pass the SQL query as a string.

Q3. What is the purpose of the question mark in a query?

Answer: The question mark is a placeholder for a parameter value that is supplied through a setter method.

Q4. Do parameter indexes start at zero?

Answer: No. JDBC parameter indexes start at 1.

Q5. Which method executes a SELECT query?

Answer: The executeQuery() method executes a query and returns a ResultSet.

Q6. Which method executes an INSERT statement?

Answer: The executeUpdate() method is generally used for INSERT statements and returns the number of affected rows.

Q7. How does PreparedStatement help prevent SQL injection?

Answer: It separates SQL instructions from bound parameter values, preventing those values from being interpreted as SQL syntax when used correctly.

Q8. Can a PreparedStatement be reused?

Answer: Yes. Its parameter values can be changed using setter methods, and the same SQL structure can be executed multiple times.

Q9. What happens when a parameter is not assigned a value?

Answer: If a required parameter has not been set, execution generally fails with an SQLException.

Q10. What is the difference between Statement and PreparedStatement?

Answer: Statement executes SQL strings directly, whereas PreparedStatement supports placeholders and parameter binding.

Q11. Can table names be passed using question mark placeholders?

Answer: No. Placeholders represent values, not SQL identifiers such as table or column names. Dynamic identifiers should be validated using an allowlist.

Q12. What is batch processing?

Answer: Batch processing groups multiple SQL operations for submission together, potentially reducing communication overhead.

Q13. Why is try-with-resources recommended?

Answer: It automatically closes JDBC resources when execution leaves the block, even if an exception occurs.

Q14. Does PreparedStatement always make a query faster?

Answer: No. Performance depends on the JDBC driver, database, query, and execution pattern. Reusing prepared statements may improve efficiency, but it is not guaranteed in every situation.

25. Quick Revision Checklist

• PreparedStatement belongs to the java.sql package.
• Create it using Connection.prepareStatement().
• Use question marks as parameter placeholders.
• Parameter indexes begin at 1.
• Use setString(), setInt(), setDouble(), and other suitable setter methods.
• Use executeQuery() for queries returning rows.
• Use executeUpdate() for INSERT, UPDATE, DELETE, and applicable DDL statements.
• Use parameter binding to reduce SQL injection risks.
• Use try-with-resources for automatic cleanup.
• Use transactions when multiple database changes require coordinated execution.
• Remember that placeholders cannot replace table names or SQL keywords.

26. Conclusion

PreparedStatement is an essential JDBC interface for executing parameterized SQL queries in Java applications. It allows developers to supply values separately from SQL instructions, making database code easier to maintain and helping prevent SQL injection when parameter binding is used correctly.

By practicing SELECT, INSERT, UPDATE, DELETE, multiple parameters, and batch processing, developers can build safer and more reliable database-driven applications. Understanding PreparedStatement is an important step toward developing enterprise Java applications that interact with relational databases.

Explore more enterprise Java concepts through the Advanced Java Training Course at JustAcademy. You can also register for a course demo or visit the JustAcademy official website.